using Dapper;
using DapperExtensions;
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using System.Data.Common;
using System.Drawing;
using DapperExtensions.Sql;
using DapperExtensions.Mapper;
using System.Reflection;
using SmartLink.IRepository;
using System.Linq.Expressions;
using SmartLink.Domain.ExpandEntity;

namespace SmartLink.Repository.Base
{
    public class RepositoryBase<T> : IRepository<T>
        where T : class, Domain.IEntity, new()
    {
        private static DapperExtensionsConfiguration dpConfig = new DapperExtensionsConfiguration(typeof(AutoClassMapper<>), new List<Assembly>(), new MySqlDialect());
        private static SqlGeneratorImpl sqlImpement = new SqlGeneratorImpl(dpConfig);

        /// <summary>
        /// 如果有事物，从事物上获取连接
        /// </summary>
        /// <param name="tran"></param>
        /// <returns></returns>
        protected static DbConnection CreateConn(DbTransaction tran = null)
        {
            DbConnection conn = null;
            if (tran == null)
            {
                conn = DapperHelper.GetDefaultConnection();
            }
            else
            {
                conn = tran.Connection;
            }
            return conn;
        }

        protected IDatabase GetIDataBase(IDbConnection cn)
        {
            IDatabase Db = new Database(cn, sqlImpement);
            return Db;
        }

        protected static void CloseConn(DbConnection conn, DbTransaction tran = null)
        {
            if (tran == null)
            {
                conn.Close();
                conn.Dispose();
            }
        }

        #region Using DapperExtensions

        /// <summary>
        /// 事务中慎用
        /// </summary>
        /// <param name="entity"></param>
        /// <returns>Identity</returns>
        public virtual dynamic Add(T entity, DbTransaction tran = null)
        {
            dynamic result = 0; ;
            DbConnection conn = CreateConn(tran);
            try
            {
                IDatabase db = GetIDataBase(conn);
                result = db.Insert(entity, tran);
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                CloseConn(conn, tran);
            }
            return result;
        }

        public virtual bool AddAll(IEnumerable<T> entities)
        {
            using (var conn = DapperHelper.GetDefaultConnection())
            {
                bool result = true;
                using (var trans = conn.BeginTransaction())
                {
                    try
                    {
                        IDatabase db = GetIDataBase(conn);
                        db.Insert(entities, trans, 30);
                        trans.Commit();
                    }
                    catch (DataException)
                    {
                        result = false;
                        trans.Rollback();
                        throw;
                    }
                }
                return result;
            }
        }

        public virtual bool AddAll(IEnumerable<T> entities, DbTransaction tran = null)
        {
            using (var conn = DapperHelper.GetDefaultConnection())
            {
                bool result = true;
                try
                {
                    IDatabase db = GetIDataBase(conn);
                    db.Insert(entities, tran, 30);
                    if(tran!=null)
                        tran.Commit();
                }
                catch (DataException)
                {
                    result = false;
                    if (tran != null)
                        tran.Rollback();
                    throw;
                }
                return result;
            }
        }

        public virtual bool AddAllOutTran(IEnumerable<T> entities, DbTransaction tran = null)
        {
            using (var conn = DapperHelper.GetDefaultConnection())
            {
                bool result = true;
                try
                {
                    IDatabase db = GetIDataBase(conn);
                    db.Insert(entities, tran, 30);
                }
                catch (DataException)
                {
                    result = false;
                    throw;
                }
                return result;
            }
        }

        public virtual IEnumerable<T> GetAll()
        {
            using (var conn = DapperHelper.GetDefaultConnection())
            {
                IDatabase db = GetIDataBase(conn);
                return db.GetList<T>();
            }
        }

        public virtual T GetById(dynamic id)
        {
            using (var conn = DapperHelper.GetDefaultConnection())
            {
                IDatabase db = GetIDataBase(conn);
                return db.Get<T>(id);
            }
        }

        public virtual bool Update(T entity, DbTransaction tran = null)
        {
            bool success;
            DbConnection conn = CreateConn(tran);
            try
            {
                IDatabase db = GetIDataBase(conn);
                success = db.Update(entity, tran);
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                CloseConn(conn, tran);
            }
            return success;
        }

        public virtual bool Update(IEnumerable<T> entities)
        {
            bool result = true;
            using (var conn = DapperHelper.GetDefaultConnection())
            {
                IDatabase db = GetIDataBase(conn);
                using (var trans = conn.BeginTransaction())
                {
                    try
                    {
                        foreach (var item in entities)
                        {
                            db.Update(item, trans);
                        }
                        trans.Commit();
                    }
                    catch (DataException)
                    {
                        result = false;
                        trans.Rollback();
                    }
                }
            }
            return result;
        }

        public virtual bool Delete(T entity, DbTransaction tran = null)
        {
            bool success;
            DbConnection conn = CreateConn(tran);
            try
            {
                IDatabase db = GetIDataBase(conn);
                success = db.Delete(entity, tran);
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                CloseConn(conn, tran);
            }
            return success;
        }

        #endregion

        public virtual IEnumerable<T> GetAll(bool containsLogicDeleted)
        {
            string sql = string.Format($"SELECT * FROM {GetTableName()} WHERE IsDel=0");
            if (containsLogicDeleted)
                sql = string.Format($"SELECT * FROM {GetTableName()}");
            using (var conn = DapperHelper.GetDefaultConnection())
            {
                var result = conn.Query<T>(sql);
                return result;
            }
        }

        public virtual void LockTableRead()
        {
            string sql = string.Format($"LOCK TABLES {GetTableName()} READ");

            using (var conn = DapperHelper.GetDefaultConnection())
            {
                conn.Execute(sql);
            }
        }

        public virtual void LockTableWrite()
        {
            string sql = string.Format($"LOCK TABLES {GetTableName()} WRITE");

            using (var conn = DapperHelper.GetDefaultConnection())
            {
                conn.Execute(sql);
            }
        }

        public virtual void UnLockTable()
        {
            string sql = string.Format($"UNLOCK TABLES");

            using (var conn = DapperHelper.GetDefaultConnection())
            {
                conn.Execute(sql);
            }
        }

        public virtual IEnumerable<T> GetBySql(string sql)
        {
            using (var conn = DapperHelper.GetDefaultConnection())
            {
                return conn.Query<T>(sql);
            }
        }

        public virtual bool Delete(dynamic primaryKey, DbTransaction tran = null)
        {
            bool result = false;
            DbConnection conn = CreateConn(tran);
            try
            {
                var sql = string.Format("delete from {0} where {1} = {2}", GetTableName(), GetPrimaryKey(), primaryKey);
                result = conn.Execute((string)sql, tran) > 0;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                CloseConn(conn, tran);
            }
            return result;
        }

        public virtual bool DeleteLogic(dynamic primaryKey, DbTransaction tran = null)
        {
            bool result = false;
            DbConnection conn = CreateConn(tran);
            try
            {
                var sql = string.Format("update {0} set  isdel= 1 where  {1} = {2}", GetTableName(), GetPrimaryKey(), primaryKey);
                result = conn.Execute((string)sql, tran) > 0;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                CloseConn(conn, tran);
            }
            return result;
        }

        public bool DeleteWhere(string sql, DbTransaction tran = null)
        {
            bool result = false;
            DbConnection conn = CreateConn(tran);
            try
            {
                result = conn.Execute(sql, tran) > 0;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                CloseConn(conn, tran);
            }
            return result;
        }

        public bool CheckExistByFiled(string fieldName, string filedValue, string tableName, bool hasDelFlag = false)
        {
            using (var conn = DapperHelper.GetDefaultConnection())
            {
                var sql = "";
                if (hasDelFlag)
                    sql = string.Format($@"SELECT count(*) from {tableName} where {fieldName}='{filedValue}' and isdel=0 ");
                else
                    sql = string.Format($@"SELECT count(*) from {tableName} where {fieldName}='{filedValue}'");
                int count = Convert.ToInt32(conn.ExecuteScalar(sql).ToString());
                CloseConn(conn);
                return count > 0 ? true : false;
            }
        }

        public IEnumerable<T> ExecuteStoredProcedure(string StoredString, object param, DbTransaction tran)
        {
            IEnumerable<T> collect;
            DbConnection conn = CreateConn(tran);
            try
            {
                collect = conn.Query<T>(StoredString,
                                       param,
                                       tran,
                                       true,
                                       null,
                                       CommandType.StoredProcedure).ToList();
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                CloseConn(conn, tran);
            }
            return collect;
        }

        public IEnumerable<T> ExecuteStoredProcedureWithParms(string StoredString, Dictionary<string, string> param, DbTransaction tran)
        {
            IEnumerable<T> collect;
            DynamicParameters p = new DynamicParameters();
            foreach (var item in param)
            {
                p.Add(item.Key, item.Value);
            }
            using (IDbConnection conn = DapperHelper.GetDefaultConnection())
            {

                collect = conn.Query<T>(StoredString,
                                        p,
                                        tran,
                                        true,
                                        null,
                                        CommandType.StoredProcedure).ToList();
            }
            return collect;
        }

        public DbTransaction TranCreate()
        {
            return DapperHelper.GetDefaultConnection().BeginTransaction();
        }

        public void TranRollback(DbTransaction tran)
        {
            if (tran != null && tran.Connection != null) tran.Rollback();
        }

        public void TranClose(DbTransaction tran)
        {
            if ((tran.Connection != null) && (tran.Connection.State != ConnectionState.Closed))
                tran.Connection.Close();
        }

        public IList<DT> GetList<DT>(PagingSearch model, out int count)
        {
            count = 0;
            IList<DT> results;
            string whereS = string.Empty;
            string orderby = string.Empty;
            var limitSql = string.Format(" limit {0},{1}", model.PageSize * (model.PageIndex - 1), model.PageSize);
            if (!string.IsNullOrEmpty(model.StrWhere))
            {
                whereS += " where " + model.StrWhere;
            }
            if (!string.IsNullOrEmpty(model.OrderType))
            {
                orderby += " order by " + model.OrderType;
            }
            using (var conn = DapperHelper.GetDefaultConnection())
            {
                var sql = string.Format($@"SELECT {model.Fields} from {model.TableName} ");
                sql = sql + whereS + orderby + limitSql;
                results = conn.Query<DT>(sql, model.Paraparameter).ToList();
                if (model.DoCount)
                {
                    var countSql = string.Format($@"SELECT count(1) from {model.TableName} ");
                    countSql = countSql + whereS;
                    count = Convert.ToInt32(conn.ExecuteScalar(countSql, model.Paraparameter).ToString());
                }
                conn.Close();
            }
            return results;
        }

        #region 私有方法

        private static string GetPrimaryKey()
        {
            return DapperHelper.GetPrimaryKey(typeof(T));
        }

        private static string GetTableName()
        {
            return DapperHelper.GetTableName(typeof(T));
        }

        #endregion

        protected IEnumerable<SelfRelationRow> SelfTreeStructure(string tableName)
        {
            using (var conn = DapperHelper.GetDefaultConnection())
            {
                conn.Execute("CALL proc_TreeStructure(@TableName)", new { TableName = tableName });
                return conn.Query<SelfRelationRow>("SELECT * FROM temp_TreeStructure");
            }
        }
    }

    public class PagingSearch
    {
        //public enum OrderType
        //{
        //    Asc =0,
        //    Desc =1
        //}

        /// <summary>
        /// 搜索的表名,多表用join
        /// </summary>
        public string TableName { get; set; }
        /// <summary>
        /// 获取的字段
        /// </summary>
        public string Fields { get; set; }

        public int PageSize { get; set; }

        public int PageIndex { get; set; }
        /// <summary>
        /// 设置排序，用户自定义排序规则  
        /// </summary>
        public string OrderType { get; set; }
        /// <summary>
        /// 查询条件  (注意:  不要加  where)  
        /// </summary>
        public string StrWhere { get; set; }
        /// <summary>
        /// 是否返回记录数,
        /// </summary>
        public bool DoCount { get; set; }
        /// <summary>
        /// 对象参数
        /// </summary>
        public object Paraparameter { get; set; }
    }
}
